# coding: UTF-8
import pymysql
import pymysql.cursors
import os

def create_table(table_name):
    db = pymysql.connect(host='10.10.149.20', user='root', password='mzy123', db='jkw_demo', port=3306, charset='utf8')
    cur = db.cursor()
    cur.execute('DROP TABLE IF EXISTS ' + table_name)
    sql = '''CREATE TABLE ''' + table_name + '''
         (id int(11) unsigned NOT NULL AUTO_INCREMENT, 
          label int(11) DEFAULT NULL, 
          content varchar(10000) COLLATE utf8_unicode_ci DEFAULT NULL, 
          PRIMARY KEY (id))'''
    cur.execute(sql)
    db.close()

def read_table(table_name):
    db = pymysql.connect(host='10.10.149.20', user='root', password='mzy123', db='jkw_demo', port=3306, charset='utf8')
    cur = db.cursor()
    #sql = "select * FROM `information_schema`.`COLUMNS` where `table_schema` = 'jkw_demo' and `table_name` = '" + table_name + "'"
          
    sql = '''SELECT * FROM ''' +"'"+ table_name+"'"
    print(sql)
    cur.execute(sql)
    results = cur.fetchall()
    for result in results:
        print(result)
        input()
    db.close()


def insert2sql(table_name, labels, contents):
    db = pymysql.connect(host='10.10.149.20', user='root', password='mzy123', db='jkw_demo', port=3306, charset='utf8')
    cur = db.cursor()
    for i, label in enumerate(labels):
        sql = '''INSERT INTO ''' + table_name + '''
            (label, content)
            VALUES (''' + str(label) + ",'" + contents[i] + "')"
        print(sql)
        try:
            cur.execute(sql)
            if i%100==0:
                db.commit()
        except:
            db.rollback()
    db.commit()
    cur.close()
    db.close()

def do():
    db = pymysql.connect(host='10.10.149.20', user='root', password='mzy123', db='jkw_demo', port=3306, charset='utf8')
    cur = db.cursor()
    sql = "alter table classification_gz add unique(content);"
    cur.execute(sql)
    db.commit()
    cur.close()
    db.close()


if __name__ == '__main__':
    # 标签 setting-category
    # 训练数据 knowledge-traindata-js
    
    #create_table('classification_gz')
    #read_table('classification_gz')
    # read_table('setting-category')
    do()
    #read_table('knowledge-traindata-js')

